﻿--获取岗位异动员工信息
CREATE proc [dbo].[proc_Employee_PostMove_GetItem]
	@id int,
	@GID uniqueidentifier
as
	declare @eid int
	set @eid = (select eid from Employee_PostMove where id=@id)

	--select
	
	--	Id,
	--	(select top 1 ENumber from Employee E where EId=E.Id) as ENumber,
	--	(select top 1  ETempNumber from Employee E where EId=E.Id) as ETempNumber,
	--	EId,
	--	(select top 1 EName from Employee E where EId=E.Id) as EName,
	--	(select top 1 Sex from Employee E where EId=E.Id) as Sex,
	--	(select top 1 IDCard from Employee E where EId=E.Id) as IDCard,
	--	(select top 1 RuZhiDate from Employee E where EId=E.Id) as RuZhiDate,
	--	IsNull((select top 1 zzdate from Employee_ProbationPassed ep where ep.eid=@eid),'1900-01-01') as zzdate,
	--	(select  CompanyName from Company where id=BCompanyId) as BCompanyName,
	--	(select  DName from Department where id=BDepartmentId) as BDName,
	--	(select P.PostName from Post P where P.id=Employee_PostMove.BPostId) as BPostName,
	--	(select pc.Title from Post_Category pc where pc.id=Employee_PostMove.BPostCategoryId) as BPostCategoryName,
	--	(select Title from Grade G where id=BGradeId) as BGradeName,
	--	IsNull(BWageNumber,'') as BWageNumber,
	--	(select CompanyName from Company where id=ACompanyId) as ACompanyName,
	--	IsNull((select DName from Department where id=ADepartmentId),'') as ADName,
	--	(select PostName from Post P where id=APostId) as APostName,
	--	(select pc.Title from Post_Category pc where pc.id=Employee_PostMove.APostCategoryId) as APostCategoryName,
	--	(select Title from Grade G where id=AGradeId) as AGradeName,
	--	IsNull(AWageNumber,'') as AWageNumber,
	--	(select Title from Employee_State S where id=AState) as AState,
	--	IsNull(Reason,'') Reason,
	--	(select top 1 EName from Employee E where Proposer=E.UserName) as Proposer,
	--	ApplionTime,
	--	FirstAudit,
	--	IsNull(FirstMemo,'') as FirstMemo,
	--	(select top 1 EName from Employee E where FirstAuditPerson=E.UserName) as FirstAuditPerson,
	--	FirstAuditTime,
	--	SecondAudit,
	--	SecondMemo,
	--	(select top 1 EName from Employee E where SecondAuditPerson=E.UserName) as SecondAuditPerson,
	--	SecondAuditTime,
	--	IsNull(BZhiWu,'') as BZhiWu,
	--	IsNull(AZhiWu,'') as AZhiWu,
	--	IsNull(ExecDate,'1900-01-01') as ExecDate,
	--	ISNULL(BXZJB,'') AS BXZJB,
	--	ISNULL(AXZJB,'') AS AXZJB

	--from Employee_PostMove
	--where eid=@eid and @GID in (select GID from Employee where id=@eid)
	select 
	Id,
		(select top 1 ENumber from Employee E where EId=E.Id) as ENumber,
		(select top 1  ETempNumber from Employee E where EId=E.Id) as ETempNumber,
		EId,
		(select top 1 EName from Employee E where EId=E.Id) as EName,
		(select top 1 Sex from Employee E where EId=E.Id) as Sex,
		(select top 1 IDCard from Employee E where EId=E.Id) as IDCard,
		(select top 1 RuZhiDate from Employee E where EId=E.Id) as RuZhiDate,
		IsNull((select top 1 zzdate from Employee_ProbationPassed ep where ep.eid=@eid),'1900-01-01') as zzdate,
		(case BCompanyName when '' then (select  CompanyName from Company where id=BCompanyId) else BCompanyName end) as BCompanyName,
		(case BDepartmentName when '' then (select  DName from Department where id=BDepartmentId) else BDepartmentName end) as BDName,
		(case BPostName when '' then (select P.PostName from Post P where P.id=Employee_PostMove.BPostId) else BPostName end) as BPostName,
		(select pc.Title from Post_Category pc where pc.id=Employee_PostMove.BPostCategoryId) as BPostCategoryName,
		(case BGradeName when '' then (select Title from Grade G where id=BGradeId) else BGradeName end) as BGradeName,
		IsNull(BWageNumber,'') as BWageNumber,
		(case ACompanyName when '' then (select CompanyName from Company where id=ACompanyId) else ACompanyName end) as ACompanyName,
		(case ADepartmentName when '' then IsNull((select DName from Department where id=ADepartmentId),'') else isnull(ADepartmentName,'') end) as ADName,
		(case APostName when '' then (select PostName from Post P where id=APostId) else APostName end) as APostName,
		(select pc.Title from Post_Category pc where pc.id=Employee_PostMove.APostCategoryId) as APostCategoryName,
		(case AGradeName when '' then (select Title from Grade G where id=AGradeId) else AGradeName end) as AGradeName,
		IsNull(AWageNumber,'') as AWageNumber,
		(select Title from Employee_State S where id=AState) as AState,
		IsNull(Reason,'') Reason,
		(select top 1 EName from Employee E where Proposer=E.UserName) as Proposer,
		ApplionTime,
		FirstAudit,
		IsNull(FirstMemo,'') as FirstMemo,
		(select top 1 EName from Employee E where FirstAuditPerson=E.UserName) as FirstAuditPerson,
		FirstAuditTime,
		SecondAudit,
		SecondMemo,
		(select top 1 EName from Employee E where SecondAuditPerson=E.UserName) as SecondAuditPerson,
		SecondAuditTime,
		IsNull(BZhiWu,'') as BZhiWu,
		IsNull(AZhiWu,'') as AZhiWu,
		IsNull(ExecDate,'1900-01-01') as ExecDate,
		ISNULL(BXZJB,'') AS BXZJB,
		ISNULL(AXZJB,'') AS AXZJB,
		(Select IsCore From Employee E where Id=@eid) OIsCore,
		IsCore
		
		from Employee_PostMove
		where eid=@eid and @GID in (select GID from Employee where id=@eid)